package com.che.software.testato.domain.dao.jdbc.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.che.software.testato.domain.dao.IItemDAO;
import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO;
import com.che.software.testato.domain.dao.jdbc.exception.ItemSearchDAOException;
import com.che.software.testato.domain.dao.jdbc.exception.ItemUpdateDAOException;
import com.che.software.testato.domain.entity.Item;
import com.che.software.testato.domain.entity.search.ItemSearch;

/**
 * JDBC implementation of the DAO interface dedicated to the projects
 * management.
 * 
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @copyright Che Software.
 * @license GNU General Public License.
 * @see AbstractDAO, IItemDAO.
 * @since July, 2011.
 * 
 *        This file is part of Testato.
 * 
 *        Testato is free software: you can redistribute it and/or modify it
 *        under the terms of the GNU General Public License as published by the
 *        Free Software Foundation, either version 3 of the License, or (at your
 *        option) any later version.
 * 
 *        Testato is distributed in the hope that it will be useful, but WITHOUT
 *        ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 *        FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
 *        for more details.
 * 
 *        You should have received a copy of the GNU General Public License
 *        along with Testato. If not, see <http://www.gnu.org/licenses/>.
 * 
 *        Testato's logo is a creation of Arrioch
 *        (http://arrioch.deviantart.com/) and it's distributed under the terms
 *        of the Creative Commons License.
 */
@Repository("itemDAO")
public class ItemDAO extends AbstractDAO implements IItemDAO {

	/**
	 * Constants.
	 */
	private static final Logger LOGGER = Logger.getLogger(ItemDAO.class);

	/**
	 * Update an item to operationalize it.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param itemId the item id.
	 * @return the created test case id.
	 * @since July, 2011.
	 * @throws ItemUpdateDAOException if an error occurs during the update.
	 */
	@Override
	public int operationalizeItem(int itemId) throws ItemUpdateDAOException {
		LOGGER.debug("operationalizeItem(" + itemId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			connection.setAutoCommit(false);
			getQueryRunner().update(connection, "INSERT INTO test_case(test_case_id) VALUES(nextval('test_case_id_seq')) ");
			Integer createdTestCase = (Integer) getQueryRunner().query(connection, "SELECT MAX(test_case_id)::int AS testCaseId FROM test_case ", new ScalarHandler("testCaseId"));
			getQueryRunner().update(connection, "UPDATE item SET test_case_id = ? WHERE item_id = ? ", new Object[] { createdTestCase, itemId });
			connection.commit();
			return createdTestCase;
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new ItemUpdateDAOException(e);
			}
			throw new ItemUpdateDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Update an item to refine it.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param itemId the item id.
	 * @return the created action plan id.
	 * @since July, 2011.
	 * @throws ItemUpdateDAOException if an error occurs during the update.
	 */
	@Override
	public int refineItem(int itemId) throws ItemUpdateDAOException {
		LOGGER.debug("refineItem(" + itemId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			connection.setAutoCommit(false);
			getQueryRunner().update(connection, "INSERT INTO action_plan(action_plan_id) VALUES(nextval('action_plan_seq')) ");
			Integer createdActionPlan = (Integer) getQueryRunner().query(connection, "SELECT MAX(action_plan_id)::int AS actionPlanId FROM action_plan ", new ScalarHandler("actionPlanId"));
			getQueryRunner().update(connection, "UPDATE item SET action_plan_id = ? WHERE item_id = ? ", new Object[] { createdActionPlan, itemId });
			connection.commit();
			return createdActionPlan;
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new ItemUpdateDAOException(e1);
			}
			throw new ItemUpdateDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Item search from a bean of criterions.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the criterions to use for the search.
	 * @return the resulting object list.
	 * @since July, 2011.
	 * @throws ItemSearchDAOException if an error occurs during the search.
	 */
	@Override
	public List<Item> searchItem(ItemSearch searchBean) throws ItemSearchDAOException {
		LOGGER.debug("searchItem().");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			return getQueryRunner().query(connection, getItemSearchQueryFromCriterion(searchBean, params), new BeanListHandler<Item>(Item.class), params.toArray());
		} catch (SQLException e) {
			throw new ItemSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Recovery of the item search query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since July, 2011.
	 */
	private String getItemSearchQueryFromCriterion(ItemSearch searchBean, List<Object> params) {
		LOGGER.debug("getItemSearchQueryFromCriterion().");
		setWhereClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("SELECT item_id AS itemId, test_case_id AS testCaseId, i.action_plan_id AS actionPlanId, i.label, iteration_max_number AS maxIterationNumber, EXISTS (SELECT action_id FROM action_exclusive_item ex WHERE ex.item_id = i.item_id) AS exclusive ");
		if (null != searchBean && 0 != searchBean.getParentActionPlanId()) {
			sBuilder.append(", inso.label AS sourceIntentionLabel, inta.label AS targetIntentionLabel, inso.intention_id AS sourceIntentionId, inta.intention_id AS targetIntentionId ");
		}
		sBuilder.append("FROM item i ");
		if (null != searchBean && 0 != searchBean.getParentActionPlanId()) {
			sBuilder.append("LEFT JOIN action_inclusive_item ai USING(item_id) LEFT JOIN action_exclusive_item ei USING(item_id) LEFT JOIN action aai ON(aai.action_id = ai.action_id) LEFT JOIN action aei ON(aei.action_id = ei.action_id) JOIN intention inso ON(inso.intention_id = COALESCE(aai.source_intention, aei.source_intention)) JOIN intention inta ON(inta.intention_id = COALESCE(aai.target_intention, aei.target_intention)) ");
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("COALESCE(aai.action_plan_id, aei.action_plan_id) = ? ");
			params.add(searchBean.getParentActionPlanId());
		}
		if (null != searchBean && 0 != searchBean.getParentAction()) {
			sBuilder.append("LEFT JOIN action_inclusive_item aii USING(item_id) LEFT JOIN action_exclusive_item aei USING(item_id) ");
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("COALESCE(aii.action_id, aei.action_id) = ? ");
			params.add(searchBean.getParentAction());
		}
		sBuilder.append("ORDER BY i.label ");
		return sBuilder.toString();
	}
}